package com.fengxing.ams.web.facade.mouble.student;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

public interface IStudentDAO extends PagingAndSortingRepository<StudentVO, String>, JpaSpecificationExecutor<StudentVO>  {

	@Query(value = "SELECT COUNT(1) FROM zyt_student WHERE college_id=:college_id AND YEAR=2015  GROUP BY student_gendar ORDER BY student_gendar", nativeQuery=true)
	List<BigInteger> getGendarTotal(@Param("college_id")Integer college_id);

	@Query(value = "SELECT COUNT(1) FROM zyt_student_wish WHERE college_id=:college_id AND YEAR=2015", nativeQuery=true)
	BigInteger getApplyStudentTotal(@Param("college_id")Integer collegeId);

	@Query(value = "SELECT COUNT(1) FROM zyt_student_wish WHERE college_id=:college_id AND YEAR=2015", nativeQuery=true)
	List<StudentVO> queryByScore(String batch, String subject, Integer score,
			Integer minScore, Integer maxScore);
	@Query(value = "SELECT                              "+
"  location,                         "+
"  SUM(enrolled) AS enrolled         "+
"FROM                                "+
"  zyt_college_base X,               "+
"  (SELECT                           "+
"    college_id,                     "+
"    COUNT(1) AS enrolled            "+
"  FROM                              "+
"    zyt_student b IGNORE INDEX(student_index_3,student_index_1,student_index_2,student_index_5,student_apply_idx)"+
"WHERE IF("+
	      ":descriptionFlag = 'true',"+
	      "b.description_id IN (:description_id),"+
	      "'1=1'"+
	    ")"+ 
"and IF(                             "+
"      :yearFlag = 'true',           "+
"      b.year IN (:year),            "+
"      '1=1'                         "+
"    )                               "+
"    AND (b.apply_score BETWEEN :minScore"+
"      AND :maxScore)                "+
"    AND IF(                         "+
"      :batchFlag = 'true',          "+
"      b.batch IN (:batch),          "+
"      '1=1'                         "+
"    )                               "+
"    AND IF(                         "+
"      :subjectFlag = 'true',        "+
"      b.subject IN (:subject),      "+
"      '1=1'                         "+
"    )                               "+
"  GROUP BY college_id) Y            "+
"WHERE x.college_id = y.college_id   "+
"GROUP BY x.location                 "+
"ORDER BY SUM(enrolled) DESC         ", nativeQuery=true)
	List<Object[]> queryStudentDisLocation(@Param("year")List<Integer> year, @Param("minScore")Integer minScore, @Param("maxScore")Integer maxScore, 
			@Param("batch")List<String> batchs, @Param("subject")List<String> subjects, @Param("description_id")List<Integer> description_ids, 
			@Param("batchFlag")String batchFlag, @Param("subjectFlag")String subjectFlag, @Param("descriptionFlag")String descriptionFlag, @Param("yearFlag")String yearFlag);

	@Query(value="SELECT                                    "+
"  college_id,                             "+
"  college_name,                           "+
"  COUNT(1) AS num                         "+
"FROM                                      "+
"  zyt_student b                           "+
"WHERE location = :location                "+
"  AND IF(                                 "+
"    :descriptionFlag = 'true',            "+
"    b.description_id IN (:description_id),"+
"    '1=1'                                 "+
"  )                                       "+
"  AND IF(                                 "+
"    :yearFlag = 'true',                   "+
"    b.year IN (:year),                    "+
"    '1=1'                                 "+
"  )                                       "+
"  AND (                                   "+
"    b.apply_score BETWEEN :minScore       "+
"    AND :maxScore                         "+
"  )                                       "+
"  AND IF(                                 "+
"    :batchFlag = 'true',                  "+
"    b.batch IN (:batch),                  "+
"    '1=1'                                 "+
"  )                                       "+
"  AND IF(                                 "+
"    :subjectFlag = 'true',                "+
"    b.subject IN (:subject),              "+
"    '1=1'                                 "+
"  )                                       "+
"GROUP BY college_id                       "+
"ORDER BY COUNT(1) DESC                    "+
"LIMIT 0, 10                               ", nativeQuery=true)
	List<Object[]> queryTop10CollegeByLocation(@Param("location")String locatoin, @Param("year")List<Integer> year, @Param("minScore")Integer minScore, @Param("maxScore")Integer maxScore, 
			@Param("batch")List<String> batchs, @Param("subject")List<String> subjects, @Param("description_id")List<Integer> description_ids, 
			@Param("batchFlag")String batchFlag, @Param("subjectFlag")String subjectFlag, @Param("descriptionFlag")String descriptionFlag, @Param("yearFlag")String yearFlag);

	@Query(value="SELECT                                    "+
"  major_id,                             "+
"  major_name,                           "+
"  COUNT(1) AS num                         "+
"FROM                                      "+
"  zyt_student b                           "+
"WHERE college_id = :college_id                "+
"  AND IF(                                 "+
"    :descriptionFlag = 'true',            "+
"    b.description_id IN (:description_id),"+
"    '1=1'                                 "+
"  )                                       "+
"  AND IF(                                 "+
"    :yearFlag = 'true',                   "+
"    b.year IN (:year),                    "+
"    '1=1'                                 "+
"  )                                       "+
"  AND (                                   "+
"    b.apply_score BETWEEN :minScore       "+
"    AND :maxScore                         "+
"  )                                       "+
"  AND IF(                                 "+
"    :batchFlag = 'true',                  "+
"    b.batch IN (:batch),                  "+
"    '1=1'                                 "+
"  )                                       "+
"  AND IF(                                 "+
"    :subjectFlag = 'true',                "+
"    b.subject IN (:subject),              "+
"    '1=1'                                 "+
"  )                                       "+
"GROUP BY major_id                       "+
"ORDER BY COUNT(1) DESC                    "+
"LIMIT 0, 10                               ", nativeQuery=true)
	List<Object[]> queryTopMajorByCollege_id(@Param("college_id")Integer college_id,
			 @Param("year")List<Integer> year, @Param("minScore")Integer minScore, @Param("maxScore")Integer maxScore, 
				@Param("batch")List<String> batchs, @Param("subject")List<String> subjects, @Param("description_id")List<Integer> description_ids, 
				@Param("batchFlag")String batchFlag, @Param("subjectFlag")String subjectFlag, @Param("descriptionFlag")String descriptionFlag, @Param("yearFlag")String yearFlag);

	@Query(value="SELECT SUBJECT, COUNT(1) AS num FROM zyt_student WHERE YEAR=:year GROUP BY SUBJECT ORDER BY COUNT(1) DESC", nativeQuery=true)
	List<Object[]> querySubjectRatio(@Param("year") Integer year);

	@Query(value="SELECT batch, COUNT(1) AS num FROM zyt_student WHERE YEAR=:year and apply_score between :minScore and :maxScore GROUP BY batch ORDER BY COUNT(1) desc", nativeQuery=true)
	List<Object[]> querySubjectRatio(@Param("year")List<Integer> year, @Param("minScore")Integer minScore,
			@Param("maxScore")Integer maxScore);

	@Query(value="SELECT                                     "+
"  a.description_id,                        "+
"  b.description_name,                      "+
"  b.subject,                               "+
"  b.category,                              "+
"  COUNT(1) AS num                          "+
"FROM                                       "+
"  zyt_student a IGNORE INDEX(student_index_3,student_index_1,student_index_2,student_index_5,student_apply_idx, student_desid_idx),                           "+
"  zyt_major_description b                  "+
"WHERE a.description_id = b.description_id  "+
"  AND (                                    "+
"    a.apply_score BETWEEN :minScore        "+
"    AND :maxScore                          "+
"  )                                        "+
"  AND IF(                                  "+
"    :yearFlag = 'true',                    "+
"    a.year IN (:year),                     "+
"    '1=1'                                  "+
"  )                                        "+
"  AND IF(                                  "+
"    :batchFlag = 'true',                   "+
"    a.batch IN (:batch),                   "+
"    '1=1'                                  "+
"  )                                        "+
"  AND IF(                                  "+
"    :subjectFlag = 'true',                 "+
"    a.subject IN (:subject),               "+
"    '1=1'                                  "+
"  )                                        "+
"GROUP BY a.description_id                  "+
"ORDER BY COUNT(1) DESC                     ", nativeQuery=true)
	List<Object[]> queryDescriptionGroup(@Param("year")List<Integer> year, @Param("minScore")Integer minScore, @Param("maxScore")Integer maxScore, 
				@Param("batch")List<String> batchs, @Param("subject")List<String> subjects, @Param("batchFlag")String batchFlag, 
				@Param("subjectFlag")String subjectFlag, @Param("yearFlag")String yearFlag);
	
	@Query(value = "SELECT "+ 
			"  b.batch,b.year,b.student_nation,b.student_gendar,b.apply_score,b.college_id,b.college_name,b.location,b.major_id,b.description_id,b.major_name "+
			"FROM "+
			"  zyt_student b "+
			"WHERE ( "+
			"    ( "+
			"      b.apply_score >= :min_score_2015 "+
			"      AND b.apply_score <= :max_score_2015 "+
			"      AND b.`year` = 2015 "+
			"    ) "+
			"    OR ( "+
			"      b.apply_score >= :min_score_2014 "+
			"      AND b.apply_score <= :max_score_2014 "+
			"      AND b.`year` = 2014 "+
			"    ) "+
			"    OR ( "+
			"      b.apply_score >= :min_score_2013 "+
			"      AND b.apply_score <= :max_score_2013 "+
			"      AND b.`year` = 2013 "+
			"    ) "+
			"  ) "+
			"  AND b.subject = :subject ", nativeQuery=true)

	List<Object[]> queryByScoreInnerEnrollment(@Param("min_score_2013")Integer min_score_2013,
			@Param("max_score_2013")Integer max_score_2013,
			@Param("min_score_2014")Integer min_score_2014,
			@Param("max_score_2014")Integer max_score_2014,
			@Param("min_score_2015")Integer min_score_2015,
			@Param("max_score_2015")Integer max_score_2015, @Param("subject")String subject);
	@Query( value = "SELECT * FROM zyt_student WHERE subject=:subject AND college_Id IN (:college_Id) AND major_id IN (:major_id) AND batch IN (:batch)", nativeQuery=true) 
	List<StudentVO> queryStudentByCollegeIdsAndMajorIds(
			@Param("college_Id")Set<Integer> collegeIdsforStudent, @Param("major_id")Set<Integer> majorIdsforStudent,
			@Param("batch")Set<String> batchsforStudent, @Param("subject")String subject);
	@Query( value = "SELECT * FROM zyt_student WHERE subject=:subject AND batch=:batch and college_id = :college_id AND major_id = :major_id order by year desc, apply_score desc", nativeQuery=true)
	List<StudentVO> queryStudentsByCollegeMajorBatchSubject(@Param("subject")String subject,
			@Param("batch")String batch, @Param("college_id")Integer college_id, @Param("major_id")Integer major_id);

	@Query(value =  "SELECT "+ 
			"  b.batch,b.year,b.student_nation,b.student_gendar,b.apply_score,b.college_id,b.college_name,b.location,b.major_id,b.description_id,b.major_name "+
			"FROM "+
			"  zyt_student b "+
          " where year=:year AND (apply_score BETWEEN :min_score AND :max_score) "+
          "  AND SUBJECT = :subject                               "+
          "  AND IF(:batch != '', b.batch = :batch, '1=1')        "+
					"  AND IF(                                              "+
					"    :collegeFlag = 'true',                             "+
					"    b.college_id IN (:college_id),                     "+
					"    '1=1'                                              "+
					"  )                                                    "+
					"  AND IF(                                              "+
					"    :descriptionFlag = 'true',                         "+
					"    b.description_id IN (:description_id),             "+
					"    '1=1'                                              "+
					"  )                                                    ", nativeQuery=true)
	List<Object[]> queryByYearScoreCollegeMajorBatch(@Param("max_score")Integer maxScore, @Param("min_score")Integer minScore,
			@Param("batch")String batch,@Param("year")Integer year,@Param("subject")String subject, 
			@Param("collegeFlag")String collegeFlag, @Param("college_id")List<Integer> collegeIds,
			@Param("descriptionFlag")String descriptionFlag, @Param("description_id")List<Integer> descriptionIds);
	
//	SELECT  
//	  b.* 
//	FROM 
//	  zyt_major_enrollment a, 
//	  zyt_student b 
//	WHERE ( 
//	    ( 
//	      a.max_score >= :min_score_2015 
//	      AND a.min_score <= :max_score_2015 
//	      AND a.`year` = 2015 
//	    ) 
//	    OR ( 
//	      a.max_score >= :min_score_2014 
//	      AND a.min_score <= :max_score_2014 
//	      AND a.`year` = 2014 
//	    ) 
//	    OR ( 
//	      a.max_score >= :min_score_2013 
//	      AND a.min_score <= :max_score_2013 
//	      AND a.`year` = 2013 
//	    ) 
//	  ) 
//	  AND a.subject = :subject 
//	  AND a.`year` = b.`year` 
//	  AND a.`college_id` = b.`college_id` 
//	  AND a.`major_id` = b.`major_id` 
//	  AND a.`batch` = b.`batch` 
//	  AND a.`subject` = b.`subject` 
	
}

